﻿using EmailClient.Model;
using ServiceStack.OrmLite;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EmailClient.ViewModel
{
    class LetterViewModel
    {
        public static async Task<ObservableCollection<Letter>> GetList()
        {
            var dbFactory = new OrmLiteConnectionFactory(App.DBPath, SqliteDialect.Provider, false);
            using (var db = dbFactory.Open())
            {
                return new ObservableCollection<Letter>(await db.SelectAsync<Letter>());
            }
        }

        public static async Task<ObservableCollection<Letter>> GetList(int accountId)
        {
            var dbFactory = new OrmLiteConnectionFactory(App.DBPath, SqliteDialect.Provider, false);
            using (var db = dbFactory.Open())
            {
                return new ObservableCollection<Letter>(await db.SelectAsync<Letter>(x => x.AccountId == accountId));                
            }
        }

        public static async Task<ObservableCollection<Letter>> GetList(EmailAccount account, int folderId)
        {
            var dbFactory = new OrmLiteConnectionFactory(App.DBPath, SqliteDialect.Provider, false);
            using (var db = dbFactory.Open())
            {
                var result = new ObservableCollection<Letter>(await db.SelectAsync<Letter>(x => x.FolderId == folderId && x.AccountId == account.Id)); 
                foreach(var item in result) item.Account = account;
                return result; 
            }
        }

        public static async Task<ObservableCollection<Letter>> GetList(EmailAccount account, int folderId, string searchString)
        {
            var dbFactory = new OrmLiteConnectionFactory(App.DBPath, SqliteDialect.Provider, false);
            using (var db = dbFactory.Open())
            {
                try
                {
                    var letters = await db.SelectAsync<Letter>(x => x.FolderId == folderId && x.AccountId == account.Id); 
                    var query = from x in letters.AsQueryable()
                                where x != null && ((x.BodyAsHTML != null && x.BodyAsHTML.Contains(searchString)) 
                                || (x.BodyAsText != null && x.BodyAsText.ToUpper().Contains(searchString))
                                || (x.ToSent != null && x.ToSent.Contains(searchString))
                                || (x.Subject != null && x.Subject.ToUpper().Contains(searchString))
                                || (x.FromSent != null && x.FromSent.Contains(searchString)))
                                select x; 

                    var result = new ObservableCollection<Letter>();
                    if (query.Any())
                    {
                        foreach (Letter item in query)
                        {
                            item.Account = account;
                            result.Add(item);
                        }
                    }
                    return result;
                }
                catch (Exception e) { }
            }
            return null;
        }

        public static async Task<Letter> Get(string messId)
        {
            var dbFactory = new OrmLiteConnectionFactory(App.DBPath, SqliteDialect.Provider, false);
            using (var db = dbFactory.Open())
            {
                var l = new List<Letter>(await db.SelectAsync<Letter>(x => x.MessageId == messId));
                return l.First<Letter>();                
            }
        }

        public static async Task<int> GetLetterId(string messId)
        {
            var dbFactory = new OrmLiteConnectionFactory(App.DBPath, SqliteDialect.Provider, false);
            using (var db = dbFactory.Open())
            {
                var l = new List<Letter>(await db.SelectAsync<Letter>(x => x.MessageId == messId));
                return l.First<Letter>().Id;
            }
        }

        public static async Task<string> InsertUpdate(Letter item)
        {
            var result = "success";
            try
            {
                var dbFactory = new OrmLiteConnectionFactory(App.DBPath, SqliteDialect.Provider, false);
                using (var db = dbFactory.Open())
                {
                    var isLetter = await db.SelectAsync<Letter>(x => x.MessageId == item.MessageId);
                    if (isLetter.Count > 0) await db.UpdateAsync(item);                  
                    else
                    {
                        await db.InsertAsync(item);
                        item.Id = await GetLetterId(item.MessageId.Trim());
                    }                   
                }
            }
            catch (Exception e) { result = e.Message; }

            return result;
        }

        public static async Task<string> Delete(Letter item)
        {
            var result = "success";
            try
            {
                var dbFactory = new OrmLiteConnectionFactory(App.DBPath, SqliteDialect.Provider, false);
                using (var db = dbFactory.Open())
                {
                    await db.DeleteAsync(item);
                }
            }
            catch (Exception e) { result = e.Message; }

            return result;
        }

        public static async Task<string> Delete(int accountId)
        {
            var result = "success";
            try
            {
                var dbFactory = new OrmLiteConnectionFactory(App.DBPath, SqliteDialect.Provider, false);
                using (var db = dbFactory.Open())
                {                    
                    await db.DeleteAsync<Letter>(x => x.AccountId == accountId);
                }
            }
            catch (Exception e) { result = e.Message; }

            return result;
        }

        public static async Task<string> Delete(int accountId, int folderId)
        {
            var result = "success";
            try
            {
                var dbFactory = new OrmLiteConnectionFactory(App.DBPath, SqliteDialect.Provider, false);
                using (var db = dbFactory.Open())
                {
                    await db.DeleteAsync<Letter>(x=>x.FolderId == folderId && x.AccountId == accountId);
                }
            }
            catch (Exception e) { result = e.Message; }

            return result;
        }
                
    }
}
